package omm.dao.imp;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.joda.time.DateTime;

import omm.dao.DatabaseStrategy;
import omm.domain.Company;
import omm.domain.Order;
import omm.domain.Quotation;
import omm.domain.Risk;

public class QuotationReportsStrategy implements DatabaseStrategy{

	@Override
	public PreparedStatement getStatement(Connection conn, DateTime beginning, DateTime ending) throws SQLException {
		Long lbeginning = beginning.getMillis()/1000;
		Long lending = ending.getMillis()/1000;
		PreparedStatement stmt = conn.prepareStatement("select c.id, c.subid, c.pedido_fk, p.nombre_cliente, c.riesgo_fk,c.compania_fk," +
				"c.comision_entidad,c.comision_aon, c.comision_porc,c.prima, c.estado, c.co_modif_user," +
				"c.co_create_user, c.co_specialist_user,c.ts, p.modif_ts,c.resolucion_fecha,c.premio," +
				"c.premio_actual, c.motivo_perdida " +
				"FROM pedido p "+
				"LEFT JOIN(select * from (select * from cotizacion where prima > 0 order by riesgo_fk, subid, estado desc, premio asc) " +
				"cot_aux group by pedido_fk, riesgo_fk, subid) c ON (p.id = c.pedido_fk) " +
				"where p.modif_ts between ? and ? " +
				"ORDER BY p.id, c.riesgo_fk, c.subid asc, c.estado desc, c.premio asc, c.id asc");
		stmt.setLong(1, lbeginning);
		stmt.setLong(2, lending);
		return stmt;
	}

	@Override
	public Quotation makeQuotation(ResultSet cur) throws SQLException {
		Integer orderID = cur.getInt("pedido_fk");
		Order order = new Order(orderID,cur.getString("nombre_cliente"));

		Integer riskID = cur.getInt("riesgo_fk");
		Risk risk = (riskID == 0) ? new Risk() : new Risk(riskID);
		Integer companyID = cur.getInt("compania_fk");
		Company company = (companyID == 0) ? null : new Company();

		Long creationUT = (long) cur.getInt("ts") * 1000;
		DateTime creationDate = (creationUT == 0) ? null
				: new DateTime(creationUT);

		Long modificationUT = (long) cur.getInt("modif_ts") * 1000;
		DateTime modificationDate = (modificationUT == 0) ? null
				: new DateTime(modificationUT);

		Long resolutionUT = (long) cur.getInt("resolucion_fecha") * 1000;
		DateTime resolutionDate = (resolutionUT == 0) ? null
				: new DateTime(resolutionUT);

		Quotation quotation = new Quotation(cur.getInt("subid"), order,
				risk, company, cur.getDouble("comision_entidad"),
				cur.getDouble("comision_aon"),
				cur.getDouble("comision_porc"), cur.getDouble("prima"),
				Quotation.State.fromCode(cur.getInt("estado")),
				cur.getString("co_modif_user"),
				cur.getString("co_create_user"),
				cur.getString("co_specialist_user"), creationDate,
				modificationDate, resolutionDate,
				cur.getDouble("premio"),
				cur.getDouble("premio_actual"),
				Quotation.LostReason.fromCode(cur
						.getInt("motivo_perdida")));
		quotation.setId(cur.getInt("id"));
		return quotation;
	}

}
